package dao.impl;

import dao.BookDao;
import dao.domain.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author huangzhiyang
 * @Date 2023/11/7
 * @Description
 */
public class BookDaoImpl extends BaseDaoImpl implements BookDao {
    /**
     * 连接数据库的部分操作
     */
    private static PreparedStatement getPreparedStatement(Book book, Connection conn, String sql) throws
            SQLException {
        PreparedStatement stmt;
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, book.getName());
        stmt.setString(2, book.getAuthor());
        stmt.setString(3, book.getPublisher());
        stmt.setDouble(4, book.getPrice());
        stmt.setInt(5, book.getQuantity());
        stmt.setBoolean(6, book.isBorrow());
        stmt.setBoolean(7, book.isSell());
        return stmt;
    }

    /**
     * 拼接SQL语句
     *
     * @param param 标签
     * @param sql   被拼接的sql语句
     * @param str   拼接好的sql语句
     */
    private static void addSql(String param, StringBuilder sql, String str) {
        if (param != null && !param.isEmpty()) {
            sql.append(str).append(param).append("%'");
        }
    }

    /**
     * 在数据库中搜索书对象数据
     *
     * @param number 书号
     * @return 书对象
     */
    @Override
    public Book selectById(int number) throws SQLException {
        Book book = null;

        Connection conn = super.getConnection();
        String sql = "SELECT * FROM book WHERE number=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setInt(1, number);
        ResultSet res = stmt.executeQuery();
        if (res != null && res.next()) {
            book = new Book(res.getInt("number"),
                    res.getString("name"),
                    res.getString("author"),
                    res.getString("publisher"),
                    res.getInt("price"),
                    res.getInt("quantity"),
                    res.getBoolean("isBorrow"),
                    res.getBoolean("isSell"));
        }
        super.closeAll(conn, stmt, res);
        return book;
    }

    /**
     * 储存数据到数据库
     *
     * @param book 储存对象
     */
    @Override
    public void save(Book book) throws SQLException {
        Connection conn = super.getConnection();
        String sql = "INSERT INTO book ( name, author, publisher, price, quantity, isBorrow, isSell) VALUES ( ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = getPreparedStatement(book, conn, sql);
        stmt.executeUpdate();
        super.closeAll(conn, stmt, null);
    }

    /**
     * 保存book数据，true为成功，false为失败
     */
    @Override
    public void update(Book book) throws SQLException {
        Connection conn = super.getConnection();
        String sql = "UPDATE book SET name=?, author=?, publisher=?, price=?, quantity=?, isBorrow=?, isSell=? WHERE number=?";
        PreparedStatement stmt = getPreparedStatement(book, conn, sql);
        stmt.setInt(8, book.getNumber());
        stmt.executeUpdate();
        super.closeAll(conn, stmt, null);

    }

    /**
     * 搜索书籍，根据多个条件进行查询
     */
    @Override
    public List<Book> searchBooks(Book book) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT * FROM book WHERE 1 = 1");
        if (book.getNumber() > 0) {
            sql.append(" AND number = ").append(book.getNumber());
        }
        addSql(book.getName(), sql, " AND name LIKE '%");
        addSql(book.getAuthor(), sql, " AND author LIKE '%");
        addSql(book.getPublisher(), sql, " AND publisher LIKE '%");
        if (book.getQuantity() > 0) {
            sql.append(" AND quantity = ").append(book.getQuantity());
        }
        if (book.getPrice() > 0) {
            sql.append(" AND price = ").append(book.getPrice());
        }
        return listBooks(sql.toString());
    }

    /**
     * 根据数据库语言搜索书籍
     *
     * @param strSql 数据库查询语言
     */
    public List<Book> listBooks(String strSql) throws SQLException {
        // 执行查询操作
        Connection connection = super.getConnection();
        PreparedStatement statement = connection.prepareStatement(strSql);
        ResultSet resultSet = statement.executeQuery();
        List<Book> bookList = new ArrayList<>();
        while (resultSet.next()) {
            int number = resultSet.getInt("number");
            String name = resultSet.getString("name");
            String author = resultSet.getString("author");
            String publisher = resultSet.getString("publisher");
            int price = resultSet.getInt("price");
            int quantity = resultSet.getInt("quantity");
            boolean isBorrow = resultSet.getBoolean("isBorrow");
            boolean isSell = resultSet.getBoolean("isSell");
            bookList.add(new Book(number, name, author, publisher, price, quantity, isBorrow, isSell));
        }
        super.closeAll(connection, statement, resultSet);
        return bookList;
    }


    /**
     * 删除书籍
     *
     * @param book 删除对象
     */
    @Override
    public boolean delete(Book book) throws SQLException {
        boolean result = false;
        Connection connection = super.getConnection();
        String sql = "DELETE FROM `book` WHERE number = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, book.getNumber());
        int rowsAffected = preparedStatement.executeUpdate();
        if (rowsAffected > 0) {
            result = true;
        }
        super.closeAll(connection, preparedStatement, null);
        return result;
    }
}
